<?php declare(strict_types=1);

/**
 * @author      guosir
 * @copyright   2023-2025 owner
 **/

namespace Srv\Apps\Apis\Promotion\Ctl;

use Srv\Apps\Proto\DTO\Promotion\StatReportListDTO;
use Srv\Apps\Proto\DTO\Promotion\StatRetainListDTO;
use Srv\Apps\Proto\DTO\Promotion\StatSqlListDTO;
use Srv\Apps\Proto\Enum\YesOrNoEnum;
use Srv\Apps\Proto\VO\Promotion\StatDataJsonVO;
use Srv\Apps\Proto\VO\Promotion\StatReportListVO;
use Srv\Apps\Proto\VO\Promotion\StatReportVO;
use Srv\Apps\Proto\VO\Promotion\StatRetainListVO;
use Srv\Apps\Proto\VO\Promotion\StatRetainVO;
use Srv\Apps\Proto\VO\Promotion\StatSqlListVO;
use Srv\Apps\Proto\VO\Promotion\StatSqlVO;
use Srv\Libs\Common\CommArray;
use Srv\Libs\Common\CommJson;
use Srv\Libs\Common\CommTime;
use Srv\Libs\Frame\ControllerAbstract;
use Srv\Libs\Frame\Page;
use Srv\Libs\Storage\DataMysql;

final class StatSqlCtl extends ControllerAbstract
{
    private ?DataMysql $DbAuto              = null;     // DB-Write



    /**
     * @return static
     * tag
     */
    final public static function tag():self
    {
        $thisObject                         = self::tagGet();
        if($thisObject instanceof self){
            $thisObject->DbAuto             = self::getDbAuto();
        }
        return $thisObject;
    }

    /**
     * @param int $sqlrId
     * @return array
     * getStatSqlSrc
     */
    public function getStatSqlSrc(int $sqlrId):array
    {
        if($sqlrId < 1) return [];
        $isFail     = false;
        $info       = $this->DbAuto->getDataOne('SELECT sqlr_id, sqlr_name, sqlr_sql, sqlr_options, sqlr_status, sqlr_exec_num, sqlr_exec_time, sqlr_create_time, sqlr_update_time FROM '.$this->DbAuto->getTableNameAll('stat_sql_record').' WHERE sqlr_id='.$sqlrId.' AND sqlr_delete=0', $isFail);
        if($isFail || !isset($info['sqlr_id'])) return [];
        return $info;
    }

    /**
     * @param Page $Page
     * @param array $whereData
     * @return StatSqlListVO|null
     * getStatSqlList
     */
    public function getStatSqlList(Page $Page, array $whereData): ?StatSqlListVO
    {
        $limitString            = $Page->getLimitString();
        $whereString            = ' WHERE sqlr_delete=0 ';
        // 检索条件
        if(count($whereData) > 0)foreach ($whereData as $key => $val){
            switch ($key){
                case StatSqlListDTO::_sqlrId:
                    $whereString    .= (strlen($whereString)>0?' AND ':'').'sqlr_id='. intval($val);
                    break;
                case StatSqlListDTO::_name:
                    $whereString    .= (strlen($whereString)>0?' AND ':'').'sqlr_name LIKE \'%'.$this->DbAuto->getRealEscapeString($val).'%\'';
                    break;
                case StatSqlListDTO::_status:
                    $whereString    .= (strlen($whereString)>0?' AND ':'').'sqlr_status='. intval($val);
                    break;
            }
        }
        $isFail                 = false;
        $sql                    = 'SELECT SQL_CALC_FOUND_ROWS sqlr_id, sqlr_name, sqlr_sql, sqlr_options, sqlr_status, sqlr_exec_num, sqlr_exec_time, sqlr_create_time, sqlr_update_time FROM '.$this->DbAuto->getTableNameAll('stat_sql_record').$whereString.' ORDER BY sqlr_id DESC '.$limitString;
        $list                   = $this->DbAuto->getData($sql, $isFail);
        $totalNum               = $this->DbAuto->getDataInt('SELECT FOUND_ROWS() as num', 'num');
        if($isFail) return null;
        $StatSqlVOList      = [];
        if(CommArray::isForeach($list)){
            foreach($list as $info){
                $sqlrIdItem         = intval($info['sqlr_id']);
                if($sqlrIdItem < 1) continue;
                $StatSqlVO          = $this->getStatSqlVO($info);
                if(!($StatSqlVO instanceof StatSqlVO)) continue;
                $StatSqlVOList[]    = $StatSqlVO;
            }
        }
        $StatSqlListVO      = new StatSqlListVO();
        $StatSqlListVO->setList($StatSqlVOList);
        $StatSqlListVO->setPage($Page->getPageVO($totalNum));
        return $StatSqlListVO;
    }

    /**
     * @param array $info
     * @return StatSqlVO|null
     * getStatSqlVO
     */
    public function getStatSqlVO(array $info):?StatSqlVO
    {
        $sqlrId             = intval($info['sqlr_id']??0);
        if($sqlrId < 1) return null;
        $StatSqlVO          = new StatSqlVO();
        $StatSqlVO->setSqlrId($sqlrId);
        $StatSqlVO->setName(trim($info['sqlr_name']));
        $StatSqlVO->setSql(trim($info['sqlr_sql']));
        $StatSqlVO->setOptions(trim($info['sqlr_options']));
        $StatSqlVO->setStatus(YesOrNoEnum::parseValue($info['sqlr_status']));
        $StatSqlVO->setExecNum(intval($info['sqlr_exec_num']));
        $StatSqlVO->setExecTime(intval($info['sqlr_exec_time']));
        $StatSqlVO->setCreateTime(intval($info['sqlr_create_time']));
        $StatSqlVO->setUpdateTime(intval($info['sqlr_update_time']));
        return $StatSqlVO;
    }


    /**
     * @param int $sqlrId
     * @param array $optionData
     * @return StatDataJsonVO|null
     * getStatDataJsonStr
     */
    public function getStatDataJsonStr(int $sqlrId, array $optionData):?StatDataJsonVO
    {
        if($sqlrId < 1 || !CommArray::isForeach($optionData)) return null;
        $StatDataJsonVO             = new StatDataJsonVO();
        $isFail                     = false;
        $sqlText                    = $optionData['sqlText'];
        $beforeMilliTime            = CommTime::getTimeMilli();
        $dataOptionsStatus          = $optionData['dataOption']['status'];
        $dataListIndexKey           = $optionData['dataOption']['indexKey'];
        $dataOptionsIndexKey        = $optionData['dataOption']['dataIndexKey'];
        $dataOptionsValueKeyList    = $optionData['dataOption']['dataValueKeyList'];
        $cellStyleList              = $optionData['cellStyleList'];
        $list                       = $this->DbAuto->getData($sqlText, $isFail);
        $afterMilliTime             = CommTime::getTimeMilli();
        $headerList                 = [];
        if(!$isFail && CommArray::isForeach($list)) {
            if($dataOptionsStatus === 1 && strlen($dataListIndexKey) > 0 && strlen($dataOptionsIndexKey) > 0 && count($dataOptionsValueKeyList) > 0){
                $dataList           = $list;
                $list               = [];
                $columnList         = array_filter(array_unique(array_column($dataList, $dataOptionsIndexKey)));
                $lineTpl            = [$optionData['dataOption']['indexKey'] => ''];
                foreach ($columnList as $item){
                    foreach ($dataOptionsValueKeyList as $valueKey){
                        $lineTpl[$item.'_'.$valueKey]  = '';
                    }
                }
                foreach ($dataList as $item){
                    $dataListIndex                      = $item[$dataListIndexKey]??'';
                    $dataIndexKey                       = $item[$dataOptionsIndexKey]??'';
                    if(strlen($dataListIndex) < 1 || strlen($dataIndexKey) < 1) continue;
                    if(!isset($list[$dataListIndex])){
                        $lineTemp                       = $lineTpl;
                        $lineTemp[$dataListIndexKey]    = $dataListIndex;
                    }else{
                        $lineTemp                       = $list[$dataListIndex];
                    }
                    foreach ($dataOptionsValueKeyList as $valueKey){
                        $lineTemp[$dataIndexKey.'_'.$valueKey]  = $item[$valueKey]??'';
                    }
                    $list[$dataListIndex]               = $lineTemp;
                }
                $list = array_values($list);
            }
            if(CommArray::isForeach($list)){
                foreach ($list as $key => $val){
                    foreach ($val as $valKey => $valVal){
                        if($key === 0) $headerList[] = strval($valKey);
                    }
                    break;
                }
            }
        }
        $dataJson           = CommJson::encodeArray(['header' => $headerList, 'list' => $list, 'cellStyleList' => $cellStyleList]);
        $StatDataJsonVO->setJsonStr($dataJson);
        $execTime           = $afterMilliTime - $beforeMilliTime;
        $currTime           = CommTime::getTimeStamp();
        $this->DbAuto->query('UPDATE '.$this->DbAuto->getTableNameAll('stat_sql_record').' SET sqlr_exec_num=sqlr_exec_num+1, sqlr_exec_time='.$execTime.', sqlr_update_time='.$currTime.' WHERE sqlr_id='.$sqlrId.' AND sqlr_delete=0 AND sqlr_status=1');
        return $StatDataJsonVO;
    }

    /**
     * @param array $statSqlSrc
     * @param string $paramsJson
     * @return array
     * getDataOptions
     */
    public function getDataOptions(array $statSqlSrc, string $paramsJson):array
    {
        $optionData                 = [];
        if(!isset($statSqlSrc['sqlr_id']) || intval($statSqlSrc['sqlr_id']) < 1) return $optionData;
        $paramsData                 = CommJson::decodeArray($paramsJson);
        $sqlText                    = $statSqlSrc['sqlr_sql']??'';
        $sqlOptions                 = CommJson::decodeArray($statSqlSrc['sqlr_options']);
        $dataOptionsStatus          = intval($sqlOptions['dataOption']['status']??0);
        $dataListIndexKey           = $sqlOptions['dataOption']['indexKey']??'';
        $dataOptionsIndexKey        = $sqlOptions['dataOption']['dataIndexKey']??'';
        $dataOptionsValueKeyList    = $sqlOptions['dataOption']['dataValueKeyList']??'';
        $cellStyleStatus            = intval($sqlOptions['cellStyle']['status']??0);
        $cellStyleList              = $cellStyleStatus === 1 ? ($sqlOptions['cellStyle']['options']??[]) : [];
        $paramsList                 = $sqlOptions['params']??[];
        if(CommArray::isForeach($paramsList)){
            // 是否需要替换参数
            $paramsDataFilter       = [];
            if(CommArray::isForeach($paramsData)){
                foreach ($paramsData as $item){
                    if(!isset($item['props']) || !isset($item['value'])) continue;
                    $paramsDataFilter[$item['props']] = $item['value'];
                }
            }
            foreach ($paramsList as $item){
                $itemKey            = $item['key']??'';
                $itemProps          = $item['props']??'';
                $itemValue          = strval($item['value']??'');
                if(strlen($itemKey) < 1 || strlen($itemProps) < 1) continue;
                $itemValue          = $paramsDataFilter[$item['props']]??$itemValue;
                $sqlText            = str_replace($itemKey, $this->DbAuto->getRealEscapeString($itemValue), $sqlText);
            }
        }
        $optionData['sqlText']          = $sqlText;
        $optionData['dataOption']       = [
                'status'                => $dataOptionsStatus,
                'indexKey'              => $dataListIndexKey,
                'dataIndexKey'          => $dataOptionsIndexKey,
                'dataValueKeyList'      => $dataOptionsValueKeyList,
        ];
        $optionData['cellStyleList']    = $cellStyleList;
        return $optionData;
    }

    /**
     * @param array $whereData
     * @return StatReportListVO|null
     * getStatReportList
     */
    public function getStatReportList(array $whereData=[]):?StatReportListVO
    {
        $orderWhereString       = ' WHERE porr_delete=0 AND porr_result=1';
        $putWhereString         = '';
        $wappWhereString        = '';
        $userSubWhere           = '';
        $userWhereString        = '';
        $userSearch             = false;
        if(CommArray::isForeach($whereData)){
            foreach ($whereData as $key => $val){
                switch ($key){
                    case StatReportListDTO::_platformList:
                        $putPlatformList        = [];
                        $wappPlatformList       = [];
                        foreach ($val as $item){
                            $platform           = intval($item);
                            if($platform > 1000){
                                $putPlatformList[]      = $platform-1000;
                            }else if($platform > 100){
                                $wappPlatformList[]     = $platform-100;
                            }
                        }
                        if(count($putPlatformList) > 0){
                            $putWhereString     .= (strlen($putWhereString) > 0?' AND ':'').'ptlr_platform IN ('.implode(',', $putPlatformList).')';
                        }
                        if(count($wappPlatformList) > 0){
                            $wappWhereString    .= (strlen($wappWhereString) > 0?' AND ':'').'walr_proxy_type IN ('.implode(',', $wappPlatformList).')';
                        }
                        break;
                    case StatReportListDTO::_key:
                        $keyStr                 = $this->DbAuto->getRealEscapeString($val);
                        $putWhereString         .= (strlen($putWhereString) > 0?' AND ':'').'ptlr_key=\''.$keyStr.'\'';
                        $wappWhereString        .= (strlen($wappWhereString) > 0?' AND ':'').'walr_key=\''.$keyStr.'\'';
                        break;
                    case StatReportListDTO::_skioIdList:
                        if(!is_array($val) || count($val) < 1) break;
                        $putWhereString         .= (strlen($putWhereString) > 0?' AND ':'').'skio_id IN ('.implode(',', $val).')';
                        $wappWhereString        .= (strlen($wappWhereString) > 0?' AND ':'').'skio_id IN ('.implode(',', $val).')';
                        break;
                    case StatReportListDTO::_timeStart:
                        $orderWhereString       .= (strlen($orderWhereString) > 0?' AND ':'').'porr_create_time>='.intval($val);
                        break;
                    case StatReportListDTO::_timeStop:
                        $orderWhereString       .= (strlen($orderWhereString) > 0?' AND ':'').'porr_create_time<='.intval($val);
                        break;
                    case StatReportListDTO::_platformType:
                        $platformType               = intval($val);
                        if($platformType === 1) {
                            // App
                            $userSearch             = true;
                            $userWhereString        .= (strlen($userWhereString) > 0?' AND ':'').'ddpt_id_first IN(1,2,3,4)';
                            $orderWhereString       .= (strlen($orderWhereString) > 0?' AND ':'').'user_id IN(SELECT user_id FROM '.$this->DbAuto->getTableNameAll('user').' WHERE ddpt_id_first IN(1,2,3,4))';
                        }else if($platformType === 2){
                            // Web
                            $userSearch             = true;
                            $userWhereString        .= (strlen($userWhereString) > 0?' AND ':'').'ddpt_id_first IN(5,6,7,8)';
                            $orderWhereString       .= (strlen($orderWhereString) > 0?' AND ':'').'user_id IN(SELECT user_id FROM '.$this->DbAuto->getTableNameAll('user').' WHERE ddpt_id_first IN(5,6,7,8))';
                        }
                        break;
                    case StatReportListDTO::_channelMatch:
                        $userSearch                 = true;
                        if($val === '1'){
                            $userWhereString        .= (strlen($userWhereString)>0?' AND ':'').'LENGTH(user_chnl_match)>0';
                        }else{
                            $userWhereString        .= (strlen($userWhereString)>0?' AND ':'').'user_chnl_match LIKE \''.$this->DbAuto->getRealEscapeString($val).'%\'';
                        }
                        break;
                    case StatReportListDTO::_pixelIdMatch:
                        $userSearch                 = true;
                        if($val === '1'){
                            $userWhereString        .= (strlen($userWhereString)>0?' AND ':'').'LENGTH(user_pixel_id_match)>0';
                        }else{
                            $userWhereString        .= (strlen($userWhereString)>0?' AND ':'').'user_pixel_id_match LIKE \''.$this->DbAuto->getRealEscapeString($val).'%\'';
                        }
                        break;
                    case StatReportListDTO::_linkIdList:
                        $putIdList              = [];
                        $wappIdList             = [];
                        foreach ($val as $item){
                            if(!is_string($item)) continue;
                            $linkIdArr          = explode('_', $item);
                            $type               = intval($linkIdArr[0]??0);
                            $linkId             = intval($linkIdArr[1]??0);
                            if($type === 1){
                                if($linkId > 0) $putIdList[]    = $linkId;
                            }else if($type === 2){
                                if($linkId > 0) $wappIdList[]   = $linkId;
                            }
                        }
                        if(count($putIdList) > 0 && count($wappIdList) > 0){
                            $userSearch         = true;
                            $userWhereString    .= (strlen($userWhereString) > 0?' AND ':'').'(ptlr_id_match IN ('.implode(',', $putIdList).') OR walr_id_match IN ('.implode(',', $wappIdList).'))';
                        }else if(count($putIdList) > 0){
                            $userSearch         = true;
                            $userWhereString    .= (strlen($userWhereString) > 0?' AND ':'').'ptlr_id_match IN ('.implode(',', $putIdList).')';
                        }else if(count($wappIdList) > 0){
                            $userSearch         = true;
                            $userWhereString    .= (strlen($userWhereString) > 0?' AND ':'').'walr_id_match IN ('.implode(',', $wappIdList).')';
                        }
                        break;
                }
            }
        }
        if(strlen($putWhereString) > 0 && strlen($wappWhereString) > 0){
            $userSearch         = true;
            $userWhereString    .=  (strlen($userWhereString)>0?' AND ':'').'(ptlr_id_first IN (SELECT ptlr_id FROM  '.$this->DbAuto->getTableNameAll('put_link_record').' WHERE ptlr_delete=0 AND '.$putWhereString.') AND user_src_type_first=1) OR (walr_id_first IN (SELECT walr_id FROM  '.$this->DbAuto->getTableNameAll('wapp_link_record').' WHERE walr_delete=0 AND '.$wappWhereString.') AND user_src_type_first=2)';
        }else if(strlen($putWhereString) > 0){
            $userSearch         = true;
            $userWhereString    .= (strlen($userWhereString)>0?' AND ':'').'ptlr_id_first IN (SELECT ptlr_id FROM  '.$this->DbAuto->getTableNameAll('put_link_record').' WHERE ptlr_delete=0 AND '.$putWhereString.') AND user_src_type_first=1';
        }else if(strlen($wappWhereString) > 0){
            $userSearch         = true;
            $userWhereString    .= (strlen($userWhereString)>0?' AND ':'').'walr_id_first IN (SELECT walr_id FROM  '.$this->DbAuto->getTableNameAll('wapp_link_record').' WHERE walr_delete=0 AND '.$wappWhereString.') AND user_src_type_first=2';
        }
        if($userSearch){
            $userSubWhere       .= (strlen($userSubWhere)>0?' AND ': '').'user_id IN (SELECT user_id FROM '.$this->DbAuto->getTableNameAll('user').' WHERE user_delete=0 AND '.$userWhereString.')';
            $orderWhereString   .= (strlen($orderWhereString) > 0?' AND ':'').$userSubWhere;
        }
        $sql                = 'SELECT FROM_UNIXTIME(porr_create_time, \'%Y-%m-%d\') as days, COUNT(DISTINCT user_id) AS user_num, COUNT(porr_id) AS order_num, SUM(porr_amount) as money_num FROM '.$this->DbAuto->getTableNameAll('pay_order_record').$orderWhereString.' GROUP BY days ORDER BY days DESC LIMIT 1000';
        $list               = $this->DbAuto->getData($sql);
        $StatReportVOList   = [];
        if(CommArray::isForeach($list)){
            // 计算首充
            $sqlFirst       = 'SELECT days, COUNT(DISTINCT user_id) AS user_num_first, SUM(porr_amount) AS money_num_first, SUM(money_num) AS money_all_first, SUM(order_num) AS order_all_first, @user_num_total := @user_num_total + COUNT(DISTINCT user_id) AS user_num_total FROM 
            (
            SELECT tmp_a.user_id, tmp_a.days, porr_amount, order_num, money_num
            FROM (
                    SELECT user_id, porr_amount, FROM_UNIXTIME(porr_create_time, \'%Y-%m-%d\') AS days, COUNT(porr_id) AS order_num, SUM(porr_amount) AS money_num FROM '.$this->DbAuto->getTableNameAll('pay_order_record').'
                    WHERE porr_result = 1 AND porr_delete=0'.(strlen($userSubWhere)>0?' AND '.$userSubWhere:'').'
                    GROUP BY user_id, days
                ) tmp_a
                LEFT JOIN (
                    SELECT user_id, FROM_UNIXTIME(porr_create_time, \'%Y-%m-%d\') AS days
                    FROM '.$this->DbAuto->getTableNameAll('pay_order_record').'
                    WHERE porr_result = 1 AND porr_delete=0'.(strlen($userSubWhere)>0?' AND '.$userSubWhere:'').'
                    GROUP BY user_id
                ) tmp_b
                ON tmp_a.user_id = tmp_b.user_id AND tmp_a.days = tmp_b.days
                WHERE tmp_b.user_id > 0
            ) tmp_pay_record, (SELECT @user_num_total := 0) tmp_num GROUP BY days ORDER BY days ASC';
            $listFirst                  = $this->DbAuto->getData($sqlFirst);
            $userNumFirstList           = array_column($listFirst, 'user_num_first', 'days');
            $moneyNumFirstList          = array_column($listFirst, 'money_num_first', 'days');
            $moneyAllFirstList          = array_column($listFirst, 'money_all_first', 'days');
            $orderAllFirstList          = array_column($listFirst, 'order_all_first', 'days');
            $userNumPayFirstTotalList   = array_column($listFirst, 'user_num_total', 'days');
            foreach ($list as $item){
                $StatReportVO           = new StatReportVO();
                if(strlen($item['days']??'') < 1) continue;
                $StatReportVO->setDays($item['days']);
                $StatReportVO->setUserNumPay(intval($item['user_num']));
                $StatReportVO->setOrderNumPay(intval($item['order_num']));
                $StatReportVO->setMoneyTotal(intval($item['money_num']));
                $StatReportVO->setUserNumPayFirst(intval($userNumFirstList[$item['days']]??'0'));
                $StatReportVO->setMoneyFirst(intval($moneyNumFirstList[$item['days']]??'0'));
                $StatReportVO->setOrderNumAllFirst(intval($orderAllFirstList[$item['days']]??'0'));
                $StatReportVO->setMoneyAllFirst(intval($moneyAllFirstList[$item['days']]??'0'));
                $StatReportVO->setUserNumPayFirstTotal(intval($userNumPayFirstTotalList[$item['days']]??'0'));
                $userNumRetain          = $StatReportVO->getUserNumPay()-$StatReportVO->getUserNumPayFirst();
                $StatReportVO->setUserNumRetain($userNumRetain);
                $moneyRetain            = $StatReportVO->getMoneyTotal()-$StatReportVO->getMoneyAllFirst();
                $StatReportVO->setMoneyRetain($moneyRetain);
                $orderNumRetain         = $StatReportVO->getOrderNumPay()-$StatReportVO->getOrderNumAllFirst();
                $StatReportVO->setOrderNumRetain($orderNumRetain);
                $StatReportVOList[]     = $StatReportVO;
            }
        }
        $StatReportListVO               = new StatReportListVO();
        $StatReportListVO->setList($StatReportVOList);
        return $StatReportListVO;
    }

    /**
     * @param array $whereData
     * @return StatRetainListVO|null
     * getStatRetainList
     */
    public function getStatRetainList(array $whereData=[]):?StatRetainListVO
    {
        $whereString        = ' WHERE 1 ';
        $tabName            = 'stat_pay_user_days';
        $createColumnName   = 'spud_day_time';
        $columnPrefix       = 'spud_';
        $isSum              = false;
        $whereStringDayOne  = ' WHERE 1';
        if(isset($whereData[StatRetainListDTO::_tabNum])){
            $tabNum                 = intval($whereData[StatRetainListDTO::_tabNum]);
            if($tabNum === 1){
                $tabName            = 'stat_pay_play_days';
                $createColumnName   = 'sppd_day_time';
                $columnPrefix       = 'sppd_';
            }else if($tabNum === 2){
                $tabName            = 'stat_nopay_play_days';
                $createColumnName   = 'snpd_day_time';
                $columnPrefix       = 'snpd_';
            }
        }
        if(isset($whereData[StatRetainListDTO::_platformType])){
            $val                    = intval($whereData[StatRetainListDTO::_platformType]);
            if($val === 1){
                $tabName            .= '_app';
            }else if($val === 2){
                $tabName            .= '_web';
            }
        }
        if(isset($whereData[StatRetainListDTO::_timeStart])){
            $isSum                  = true;
            $val                    = intval($whereData[StatRetainListDTO::_timeStart]);
            $whereString            .= (strlen($whereString) > 0?' AND ':'').$createColumnName.'>='.$val;
            $whereStringDayOne      .= (strlen($whereStringDayOne) > 0?' AND ':'').$createColumnName.'>='.($val-86400);
        }
        if(isset($whereData[StatRetainListDTO::_timeStop])){
            $isSum                  = true;
            $val                    = intval($whereData[StatRetainListDTO::_timeStop]);
            $whereString            .= (strlen($whereString) > 0?' AND ':'').$createColumnName.'<='.$val;
            $whereStringDayOne      .= (strlen($whereStringDayOne) > 0?' AND ':'').$createColumnName.'<='.($val-86400);
        }
        $list               = [];
        if($isSum) {
            $columnString   = $this->getTabColumnString($columnPrefix, true);
            $sql            = 'SELECT ' . $columnString . ' FROM ' . $this->DbAuto->getTableNameAll($tabName) . $whereString;
            $listOne        = $this->DbAuto->getDataOne($sql);
            if(CommArray::isForeach($listOne)){
                $atUserNum                          = $this->DbAuto->getDataInt('SELECT SUM('.$columnPrefix.'user_num) AS at_user_num FROM ' . $this->DbAuto->getTableNameAll($tabName) . $whereStringDayOne, 'at_user_num');
                $listOne[$columnPrefix.'day_time']  = CommTime::getString('m-d', intval($whereData[StatRetainListDTO::_timeStart])).'~'.CommTime::getString('m-d', intval($whereData[StatRetainListDTO::_timeStop]));
                $listOne['at_user_num']             = $atUserNum;
                $list[]                             = $listOne;
            }
        } else {
            $sql            = 'SELECT tmp_data.*, tmp_num.at_user_num FROM (SELECT * FROM ' . $this->DbAuto->getTableNameAll($tabName) . ') AS tmp_data LEFT JOIN (SELECT ' . $columnPrefix . 'day_time, ' . $columnPrefix . 'user_num AS at_user_num FROM ' . $this->DbAuto->getTableNameAll($tabName) . ') AS tmp_num ON tmp_data.' . $columnPrefix . 'day_time-86400=tmp_num.' . $columnPrefix . 'day_time ORDER BY ' . $createColumnName . ' DESC LIMIT 1000';
            $list           = $this->DbAuto->getData($sql);
        }
        $StatRetainVOList   = [];
        if(CommArray::isForeach($list)){
            foreach ($list as $item){
                $StatRetainVO   = new StatRetainVO();
                $atUserNum      = intval($item['at_user_num']);
                $StatRetainVO->setUserNum(intval($item[$columnPrefix.'user_num']));
                if(is_numeric($item[$columnPrefix.'day_time'])){
                    $StatRetainVO->setDays(CommTime::getDate(intval($item[$columnPrefix.'day_time'])));
                }else{
                    $StatRetainVO->setDays($item[$columnPrefix.'day_time']);
                }
                if($atUserNum > 0){
                    $StatRetainVO->setD1(sprintf('%.2f', intval($item[$columnPrefix.'user_num_1'])/$atUserNum*100));
                    $StatRetainVO->setD2(sprintf('%.2f', intval($item[$columnPrefix.'user_num_2'])/$atUserNum*100));
                    $StatRetainVO->setD3(sprintf('%.2f', intval($item[$columnPrefix.'user_num_3'])/$atUserNum*100));
                    $StatRetainVO->setD4(sprintf('%.2f', intval($item[$columnPrefix.'user_num_4'])/$atUserNum*100));
                    $StatRetainVO->setD5(sprintf('%.2f', intval($item[$columnPrefix.'user_num_5'])/$atUserNum*100));
                    $StatRetainVO->setD6(sprintf('%.2f', intval($item[$columnPrefix.'user_num_6'])/$atUserNum*100));
                    $StatRetainVO->setD7(sprintf('%.2f', intval($item[$columnPrefix.'user_num_7'])/$atUserNum*100));
                    $StatRetainVO->setD8(sprintf('%.2f', intval($item[$columnPrefix.'user_num_8'])/$atUserNum*100));
                    $StatRetainVO->setD9(sprintf('%.2f', intval($item[$columnPrefix.'user_num_9'])/$atUserNum*100));
                    $StatRetainVO->setD10(sprintf('%.2f', intval($item[$columnPrefix.'user_num_10'])/$atUserNum*100));
                    $StatRetainVO->setD11(sprintf('%.2f', intval($item[$columnPrefix.'user_num_11'])/$atUserNum*100));
                    $StatRetainVO->setD12(sprintf('%.2f', intval($item[$columnPrefix.'user_num_12'])/$atUserNum*100));
                    $StatRetainVO->setD13(sprintf('%.2f', intval($item[$columnPrefix.'user_num_13'])/$atUserNum*100));
                    $StatRetainVO->setD14(sprintf('%.2f', intval($item[$columnPrefix.'user_num_14'])/$atUserNum*100));
                    $StatRetainVO->setD15(sprintf('%.2f', intval($item[$columnPrefix.'user_num_15'])/$atUserNum*100));
                    $StatRetainVO->setD16(sprintf('%.2f', intval($item[$columnPrefix.'user_num_16'])/$atUserNum*100));
                    $StatRetainVO->setD17(sprintf('%.2f', intval($item[$columnPrefix.'user_num_17'])/$atUserNum*100));
                    $StatRetainVO->setD18(sprintf('%.2f', intval($item[$columnPrefix.'user_num_18'])/$atUserNum*100));
                    $StatRetainVO->setD19(sprintf('%.2f', intval($item[$columnPrefix.'user_num_19'])/$atUserNum*100));
                    $StatRetainVO->setD20(sprintf('%.2f', intval($item[$columnPrefix.'user_num_20'])/$atUserNum*100));
                    $StatRetainVO->setD21(sprintf('%.2f', intval($item[$columnPrefix.'user_num_21'])/$atUserNum*100));
                    $StatRetainVO->setD22(sprintf('%.2f', intval($item[$columnPrefix.'user_num_22'])/$atUserNum*100));
                    $StatRetainVO->setD23(sprintf('%.2f', intval($item[$columnPrefix.'user_num_23'])/$atUserNum*100));
                    $StatRetainVO->setD24(sprintf('%.2f', intval($item[$columnPrefix.'user_num_24'])/$atUserNum*100));
                    $StatRetainVO->setD25(sprintf('%.2f', intval($item[$columnPrefix.'user_num_25'])/$atUserNum*100));
                    $StatRetainVO->setD26(sprintf('%.2f', intval($item[$columnPrefix.'user_num_26'])/$atUserNum*100));
                    $StatRetainVO->setD27(sprintf('%.2f', intval($item[$columnPrefix.'user_num_27'])/$atUserNum*100));
                    $StatRetainVO->setD28(sprintf('%.2f', intval($item[$columnPrefix.'user_num_28'])/$atUserNum*100));
                    $StatRetainVO->setD29(sprintf('%.2f', intval($item[$columnPrefix.'user_num_29'])/$atUserNum*100));
                    $StatRetainVO->setD30(sprintf('%.2f', intval($item[$columnPrefix.'user_num_30'])/$atUserNum*100));
                    $StatRetainVO->setD31(sprintf('%.2f', intval($item[$columnPrefix.'user_num_31'])/$atUserNum*100));
                    $StatRetainVO->setD32(sprintf('%.2f', intval($item[$columnPrefix.'user_num_32'])/$atUserNum*100));
                    $StatRetainVO->setD33(sprintf('%.2f', intval($item[$columnPrefix.'user_num_33'])/$atUserNum*100));
                    $StatRetainVO->setD34(sprintf('%.2f', intval($item[$columnPrefix.'user_num_34'])/$atUserNum*100));
                    $StatRetainVO->setD35(sprintf('%.2f', intval($item[$columnPrefix.'user_num_35'])/$atUserNum*100));
                    $StatRetainVO->setD36(sprintf('%.2f', intval($item[$columnPrefix.'user_num_36'])/$atUserNum*100));
                    $StatRetainVO->setD37(sprintf('%.2f', intval($item[$columnPrefix.'user_num_37'])/$atUserNum*100));
                    $StatRetainVO->setD38(sprintf('%.2f', intval($item[$columnPrefix.'user_num_38'])/$atUserNum*100));
                    $StatRetainVO->setD39(sprintf('%.2f', intval($item[$columnPrefix.'user_num_39'])/$atUserNum*100));
                    $StatRetainVO->setD40(sprintf('%.2f', intval($item[$columnPrefix.'user_num_40'])/$atUserNum*100));
                    $StatRetainVO->setD41(sprintf('%.2f', intval($item[$columnPrefix.'user_num_41'])/$atUserNum*100));
                    $StatRetainVO->setD42(sprintf('%.2f', intval($item[$columnPrefix.'user_num_42'])/$atUserNum*100));
                    $StatRetainVO->setD43(sprintf('%.2f', intval($item[$columnPrefix.'user_num_43'])/$atUserNum*100));
                    $StatRetainVO->setD44(sprintf('%.2f', intval($item[$columnPrefix.'user_num_44'])/$atUserNum*100));
                    $StatRetainVO->setD45(sprintf('%.2f', intval($item[$columnPrefix.'user_num_45'])/$atUserNum*100));
                    $StatRetainVO->setD46(sprintf('%.2f', intval($item[$columnPrefix.'user_num_46'])/$atUserNum*100));
                    $StatRetainVO->setD47(sprintf('%.2f', intval($item[$columnPrefix.'user_num_47'])/$atUserNum*100));
                    $StatRetainVO->setD48(sprintf('%.2f', intval($item[$columnPrefix.'user_num_48'])/$atUserNum*100));
                    $StatRetainVO->setD49(sprintf('%.2f', intval($item[$columnPrefix.'user_num_49'])/$atUserNum*100));
                    $StatRetainVO->setD50(sprintf('%.2f', intval($item[$columnPrefix.'user_num_50'])/$atUserNum*100));
                    $StatRetainVO->setD51(sprintf('%.2f', intval($item[$columnPrefix.'user_num_51'])/$atUserNum*100));
                    $StatRetainVO->setD52(sprintf('%.2f', intval($item[$columnPrefix.'user_num_52'])/$atUserNum*100));
                    $StatRetainVO->setD53(sprintf('%.2f', intval($item[$columnPrefix.'user_num_53'])/$atUserNum*100));
                    $StatRetainVO->setD54(sprintf('%.2f', intval($item[$columnPrefix.'user_num_54'])/$atUserNum*100));
                    $StatRetainVO->setD55(sprintf('%.2f', intval($item[$columnPrefix.'user_num_55'])/$atUserNum*100));
                    $StatRetainVO->setD56(sprintf('%.2f', intval($item[$columnPrefix.'user_num_56'])/$atUserNum*100));
                    $StatRetainVO->setD57(sprintf('%.2f', intval($item[$columnPrefix.'user_num_57'])/$atUserNum*100));
                    $StatRetainVO->setD58(sprintf('%.2f', intval($item[$columnPrefix.'user_num_58'])/$atUserNum*100));
                    $StatRetainVO->setD59(sprintf('%.2f', intval($item[$columnPrefix.'user_num_59'])/$atUserNum*100));
                    $StatRetainVO->setD60(sprintf('%.2f', intval($item[$columnPrefix.'user_num_60'])/$atUserNum*100));
                    $StatRetainVO->setD65(sprintf('%.2f', intval($item[$columnPrefix.'user_num_65'])/$atUserNum*100));
                    $StatRetainVO->setD70(sprintf('%.2f', intval($item[$columnPrefix.'user_num_70'])/$atUserNum*100));
                    $StatRetainVO->setD75(sprintf('%.2f', intval($item[$columnPrefix.'user_num_75'])/$atUserNum*100));
                    $StatRetainVO->setD80(sprintf('%.2f', intval($item[$columnPrefix.'user_num_80'])/$atUserNum*100));
                    $StatRetainVO->setD85(sprintf('%.2f', intval($item[$columnPrefix.'user_num_85'])/$atUserNum*100));
                    $StatRetainVO->setD90(sprintf('%.2f', intval($item[$columnPrefix.'user_num_90'])/$atUserNum*100));
                    $StatRetainVO->setD95(sprintf('%.2f', intval($item[$columnPrefix.'user_num_95'])/$atUserNum*100));
                    $StatRetainVO->setD100(sprintf('%.2f', intval($item[$columnPrefix.'user_num_100'])/$atUserNum*100));
                    $StatRetainVO->setD105(sprintf('%.2f', intval($item[$columnPrefix.'user_num_105'])/$atUserNum*100));
                    $StatRetainVO->setD110(sprintf('%.2f', intval($item[$columnPrefix.'user_num_110'])/$atUserNum*100));
                    $StatRetainVO->setD115(sprintf('%.2f', intval($item[$columnPrefix.'user_num_115'])/$atUserNum*100));
                    $StatRetainVO->setD120(sprintf('%.2f', intval($item[$columnPrefix.'user_num_120'])/$atUserNum*100));
                    $StatRetainVO->setD125(sprintf('%.2f', intval($item[$columnPrefix.'user_num_125'])/$atUserNum*100));
                    $StatRetainVO->setD130(sprintf('%.2f', intval($item[$columnPrefix.'user_num_130'])/$atUserNum*100));
                    $StatRetainVO->setD135(sprintf('%.2f', intval($item[$columnPrefix.'user_num_135'])/$atUserNum*100));
                    $StatRetainVO->setD140(sprintf('%.2f', intval($item[$columnPrefix.'user_num_140'])/$atUserNum*100));
                    $StatRetainVO->setD145(sprintf('%.2f', intval($item[$columnPrefix.'user_num_145'])/$atUserNum*100));
                    $StatRetainVO->setD150(sprintf('%.2f', intval($item[$columnPrefix.'user_num_150'])/$atUserNum*100));
                    $StatRetainVO->setD155(sprintf('%.2f', intval($item[$columnPrefix.'user_num_155'])/$atUserNum*100));
                    $StatRetainVO->setD160(sprintf('%.2f', intval($item[$columnPrefix.'user_num_160'])/$atUserNum*100));
                    $StatRetainVO->setD165(sprintf('%.2f', intval($item[$columnPrefix.'user_num_165'])/$atUserNum*100));
                    $StatRetainVO->setD170(sprintf('%.2f', intval($item[$columnPrefix.'user_num_170'])/$atUserNum*100));
                    $StatRetainVO->setD175(sprintf('%.2f', intval($item[$columnPrefix.'user_num_175'])/$atUserNum*100));
                    $StatRetainVO->setD180(sprintf('%.2f', intval($item[$columnPrefix.'user_num_180'])/$atUserNum*100));
                }else{
                    $StatRetainVO->setD1('0.00');
                    $StatRetainVO->setD2('0.00');
                    $StatRetainVO->setD3('0.00');
                    $StatRetainVO->setD4('0.00');
                    $StatRetainVO->setD5('0.00');
                    $StatRetainVO->setD6('0.00');
                    $StatRetainVO->setD7('0.00');
                    $StatRetainVO->setD8('0.00');
                    $StatRetainVO->setD9('0.00');
                    $StatRetainVO->setD10('0.00');
                    $StatRetainVO->setD11('0.00');
                    $StatRetainVO->setD12('0.00');
                    $StatRetainVO->setD13('0.00');
                    $StatRetainVO->setD14('0.00');
                    $StatRetainVO->setD15('0.00');
                    $StatRetainVO->setD16('0.00');
                    $StatRetainVO->setD17('0.00');
                    $StatRetainVO->setD18('0.00');
                    $StatRetainVO->setD19('0.00');
                    $StatRetainVO->setD20('0.00');
                    $StatRetainVO->setD21('0.00');
                    $StatRetainVO->setD22('0.00');
                    $StatRetainVO->setD23('0.00');
                    $StatRetainVO->setD24('0.00');
                    $StatRetainVO->setD25('0.00');
                    $StatRetainVO->setD26('0.00');
                    $StatRetainVO->setD27('0.00');
                    $StatRetainVO->setD28('0.00');
                    $StatRetainVO->setD29('0.00');
                    $StatRetainVO->setD30('0.00');
                    $StatRetainVO->setD31('0.00');
                    $StatRetainVO->setD32('0.00');
                    $StatRetainVO->setD33('0.00');
                    $StatRetainVO->setD34('0.00');
                    $StatRetainVO->setD35('0.00');
                    $StatRetainVO->setD36('0.00');
                    $StatRetainVO->setD37('0.00');
                    $StatRetainVO->setD38('0.00');
                    $StatRetainVO->setD39('0.00');
                    $StatRetainVO->setD40('0.00');
                    $StatRetainVO->setD41('0.00');
                    $StatRetainVO->setD42('0.00');
                    $StatRetainVO->setD43('0.00');
                    $StatRetainVO->setD44('0.00');
                    $StatRetainVO->setD45('0.00');
                    $StatRetainVO->setD46('0.00');
                    $StatRetainVO->setD47('0.00');
                    $StatRetainVO->setD48('0.00');
                    $StatRetainVO->setD49('0.00');
                    $StatRetainVO->setD50('0.00');
                    $StatRetainVO->setD51('0.00');
                    $StatRetainVO->setD52('0.00');
                    $StatRetainVO->setD53('0.00');
                    $StatRetainVO->setD54('0.00');
                    $StatRetainVO->setD55('0.00');
                    $StatRetainVO->setD56('0.00');
                    $StatRetainVO->setD57('0.00');
                    $StatRetainVO->setD58('0.00');
                    $StatRetainVO->setD59('0.00');
                    $StatRetainVO->setD60('0.00');
                    $StatRetainVO->setD65('0.00');
                    $StatRetainVO->setD70('0.00');
                    $StatRetainVO->setD75('0.00');
                    $StatRetainVO->setD80('0.00');
                    $StatRetainVO->setD85('0.00');
                    $StatRetainVO->setD90('0.00');
                    $StatRetainVO->setD95('0.00');
                    $StatRetainVO->setD100('0.00');
                    $StatRetainVO->setD105('0.00');
                    $StatRetainVO->setD110('0.00');
                    $StatRetainVO->setD115('0.00');
                    $StatRetainVO->setD120('0.00');
                    $StatRetainVO->setD125('0.00');
                    $StatRetainVO->setD130('0.00');
                    $StatRetainVO->setD135('0.00');
                    $StatRetainVO->setD140('0.00');
                    $StatRetainVO->setD145('0.00');
                    $StatRetainVO->setD150('0.00');
                    $StatRetainVO->setD155('0.00');
                    $StatRetainVO->setD160('0.00');
                    $StatRetainVO->setD165('0.00');
                    $StatRetainVO->setD170('0.00');
                    $StatRetainVO->setD175('0.00');
                    $StatRetainVO->setD180('0.00');
                }
                $StatRetainVOList[]     = $StatRetainVO;
            }
        }
        $StatRetainListVO   = new StatRetainListVO();
        $StatRetainListVO->setList($StatRetainVOList);
        return $StatRetainListVO;
    }

    /**
     * @param string $prefix
     * @param bool $isSum
     * @return string
     * getTabColumnString
     */
    private function getTabColumnString(string $prefix, bool $isSum=false):string
    {
        $defNumList         = [
            0,1,2,3,4,5,6,7,8,9,
            10,11,12,13,14,15,16,17,18,19,
            20,21,22,23,24,25,26,27,28,29,
            30,31,32,33,34,35,36,37,38,39,
            40,41,42,43,44,45,46,47,48,49,
            50,51,52,53,54,55,56,57,58,59,60,
            65,70,75,80,85,90,95,100,105,110,115,120,125,130,135,140,145,150,155,160,165,170,175,180];
        if($isSum){
            $columnString       = '';
            $userNumStr         = $prefix.'user_num';
            foreach ($defNumList as $num ) {
                $columnString   .= (strlen($columnString)>0?', ':'').'SUM('.$userNumStr.($num>0?('_'.$num):'').') AS '.$userNumStr.($num>0?('_'.$num):'');
            }
        }else{
            $columnString       = $prefix.'day_time';
            $userNumStr         = $prefix.'user_num';
            foreach ($defNumList as $num ) {
                $columnString   .= (strlen($columnString)>0?', ':'').$userNumStr.($num>0?('_'.$num):'');
            }
        }
        return $columnString;
    }
}